package cn.tedu.jdbc.dao;

import cn.tedu.jdbc.bean.Employee;
import cn.tedu.jdbc.utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 完成员工表t_employee中数据的增删改查。
 * 增删改查也被简称为：CRUD
 * C：Create
 * R：Read
 * U：Update
 * D：Delete
 *
 * 注意：
 * DAO:不负责任何业务逻辑的处理，只负责数据的增删改查。
 * DAO是javaEE的设计模式之一
 * DAO中方法名起名也有讲究，一般都是以：insert delete update select开头
 */
public class EmployeeDao extends BaseDao{
    /**
     * 新增员工
     * @param employee 员工数据
     * @return 1表示新增了1条记录，返回其他值表示新增失败
     */
    public int insert (Employee employee) {
        /*Connection conn = null;
        PreparedStatement ps = null;

        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into t_employee(name,job,hiredate,salary,address) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, employee.getName());
            ps.setString(2, employee.getJob());
            ps.setString(3, employee.getHiredate());
            ps.setDouble(4, employee.getSalary());
            ps.setString(5, employee.getAddress());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,null);
        }

        return count;*/
        String sql ="insert into t_employee(name,job,hiredate,salary,address) values(?,?,?,?,?)";
        int count = executeUpdate(sql, employee.getName(), employee.getJob(), employee.getSalary(), employee.getHiredate(), employee.getAddress());
        return count;
    }

    /**
     * 根据id删除员工信息
     * @param id 员工id
     * @return 1表示删除了1条记录，返回其他值表示删除失败
     */
    public int deleteById (long id) {
        /*Connection conn = null;
        PreparedStatement ps = null;

        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,null);
        }
        return count;*/
        String sql = "delete from t_employee where id = ?";
        /*int count = executeUpdate(sql, id);
        return count;*/
        return executeUpdate(sql, id);

    }

    /**
     * 修改员工信息
     * @param newEmployee 新的员工信息（注意：新旧员工信息的id是不变的）
     * @return 1表示修改了1条记录，返回其他值表示修改失败
     */
    public int update (Employee newEmployee) {
        /*Connection conn = null;
        PreparedStatement ps = null;

        int count = 0;
        try {
            conn = DbUtils.getConnection();
            String sql = "update t_employee set name=?, job=?, hiredate=?, salary=?, address=? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, newEmployee.getName());
            ps.setString(2, newEmployee.getJob());
            ps.setString(3, newEmployee.getHiredate());
            ps.setDouble(4, newEmployee.getSalary());
            ps.setString(5, newEmployee.getAddress());
            ps.setLong(6, newEmployee.getId());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,null);
        }
        return count;*/
        String sql = "update t_employee set name=?, job=?, hiredate=?, salary=?, address=? where id = ?";
        /*int count = executeUpdate(sql, newEmployee.getName(), newEmployee.getJob(), newEmployee.getHiredate(), newEmployee.getSalary(), newEmployee.getAddress(), newEmployee.getId());
         return count;*/
        return executeUpdate(sql, newEmployee.getName(), newEmployee.getJob(), newEmployee.getHiredate(), newEmployee.getSalary(), newEmployee.getAddress(), newEmployee.getId());

    }

    /**
     * 查询员工信息
     * @param id   根据员工id查询员工信息
     * @return 返回员工信息，如果返回null，表示查询失败
     */
    public Employee select (long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        Employee employee = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                //  将数据库表查询出来的数据封装到一个Employee对象中
                employee = new Employee();
                employee.setId(id);
                employee.setName(rs.getString("name"));
                employee.setJob(rs.getString("job"));
                employee.setHiredate(rs.getString("hiredate"));
                employee.setSalary(rs.getDouble("salary"));
                employee.setAddress(rs.getString("address"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,rs);
        }

        return employee;
    }

    /**
     * 获取所有员工信息
     * @return  员工列表
     */
    public List<Employee> selectAll () {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet  rs = null;
        List<Employee> employeesList = new ArrayList<>();

        try {
            conn = DbUtils.getConnection();
            String sql = "select id,name,job,hiredate,salary,address from t_employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Employee employee = new Employee();
                employee.setId(rs.getLong("id"));
                employee.setName(rs.getString("name"));
                employee.setJob(rs.getString("job"));
                employee.setHiredate(rs.getString("hiredate"));
                employee.setSalary(rs.getDouble("salary"));
                employee.setAddress(rs.getString("address"));
                employeesList.add(employee);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,rs);
        }
        return employeesList;
    }
}
